package com.code.gen.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.code.gen.bean.bo.CodeGenProperties;
import com.code.gen.bean.bo.CodeGenPropertiesSystem;
import com.code.gen.bean.bo.database.CodeGenField;
import com.code.gen.bean.bo.database.CodeGenTable;
import com.code.gen.dao.DbDao;
import com.code.gen.database.CodeGenDatabase;
import com.code.gen.enums.ECodeGenDatabaseReadType;
import com.code.gen.enums.ECodeGenDatabaseType;
import com.code.gen.enums.ECodeGenFieldEquals;
import com.sojson.enums.core.EGeneralYesNo;
import com.sojson.util.ExpUtil;
import com.sojson.util.SpringUtil;
import com.sojson.util.StringUtil;
import com.sojson.util.db.DbUtil;
import com.sojson.util.db.bean.DatabaseBean;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;

/**
 * Database工具类
 * 
 * @author liu
 * @Date 2020-06-15 14:45:59
 * @Description
 *
 */
@Data
@Slf4j
public class CodeGenDatabaseUtils {

    private static ClassPathXmlApplicationContext CONTEXT = null;
    private static final DbDao<?> DB_DAO = getDbDao();
    private CodeGenProperties properties;
    private CodeGenPropertiesSystem system;

    public CodeGenDatabaseUtils(CodeGenProperties properties) {
        this.properties = properties;
        this.system = properties.getSystem();
    }

    /**
     * 从数据库读取数据
     * 
     * @throws Exception
     */
    public void readDatabase() throws Exception {
        if (system.getReadType() == ECodeGenDatabaseReadType.ONLINE) {
            // 设置数据库类型
            setDbType();
            // 设置数据库名称
            setDatabase();
            // 设置表信息
            setTable();
            // 设置字段信息
            setField();
        } else if (system.getReadType() == ECodeGenDatabaseReadType.OFFLINE) {
            setDbType(getDataSource());
            // 设置数据库名称
            setDatabase();
            // 设置表信息
            setTable();
            // 设置字段信息
            setField();
            // 初始化表
            init();
        } else if (system.getReadType() == ECodeGenDatabaseReadType.OTHER) {
            DatabaseBean connection = DbUtil.getConnection(system.getUrl(), system.getUsername(), system.getPassword());
            Connection conn = connection.getConn();
            try {
                // 设置数据库类型
                setDbType(conn);
                // 设置数据库名称
                setDatabase(conn);
                // 设置表信息
                setTable(conn);
                // 设置字段信息
                setField(conn);
                // 初始化表
                init();
            } finally {
                if (conn != null) {
                    conn.close();
                }
            }
        }
        // 对字段进行排序
        fieldSort();
    }

    /**
     * 获取数据库类型
     * 
     * @throws Exception
     */
    private void setDbType() throws Exception {
        setDbType(DbUtil.getDBType());
    }

    /**
     * 获取数据库类型
     * 
     * @param dataSource
     * @throws Exception
     */
    private void setDbType(DataSource dataSource) throws Exception {
        setDbType(DbUtil.getDBType(dataSource));
    }

    /**
     * 获取数据库类型
     * 
     * @param dbType    数据库类型字符串
     * @throws Exception
     */
    private void setDbType(String dbType) throws Exception {
        ECodeGenDatabaseType[] values = ECodeGenDatabaseType.values();
        for (ECodeGenDatabaseType databaseType : values) {
            if (databaseType.getName().equals(dbType)) {
                CodeGenDatabase database = properties.getDatabase();
                database.setDatabaseEnvironment(databaseType);
                database.setSsl(databaseType.getDatabaseProperties().getDatabaseSsl());
                return;
            }
        }
        ExpUtil.throwEx("数据库类型不存在,请创建后再试!");
    }

    /**
     * 获取数据库信息
     */
    private void setDatabase() {
        properties.getDatabase().setDatabaseName(DB_DAO.getDatabaseName());
    }

    /**
     * 获取表信息
     */
    private void setTable() {
        List<CodeGenTable> tables = DB_DAO.getTables(properties.getDatabase().getDatabaseName(), system.getTables());
        for (CodeGenTable table : tables) {
            if (StringUtil.isBlankString(table.getId())) {
                table.setId(table.getTableName());
            }
            table.setCreateSql(DB_DAO.getTableSql(table.getTableName()).get("Create Table"));
        }
        properties.getDatabase().setTables(tables);
    }

    /**
     * 获取字段信息
     */
    private void setField() {
        for (CodeGenTable table : properties.getDatabase().getTables()) {
            List<CodeGenField> fields = DB_DAO.getFields(properties.getDatabase().getDatabaseName(), table.getId());
            for (CodeGenField field : fields) {
                // ================================================================
                field.setColumnName(field.getColumnName().toLowerCase());
                String fieldType = field.getColumnType();
                if (fieldType.lastIndexOf("(") > 0) {
                    fieldType = fieldType.substring(0, fieldType.lastIndexOf("("));
                }
                for (ECodeGenFieldEquals fieldEquals : ECodeGenFieldEquals.values()) {
                    if (fieldType.equalsIgnoreCase(fieldEquals.getDatabase())) {
                        field.setFieldEquals(fieldEquals);
                    }
                }
            }
            table.setFields(fields);

            setPkColumn(table);
            setSerialNumberColumn(table);
            setDelFlageColumn(table);
        }
    }

    /**
     * 设置主键列信息
     *
     * @param table   业务表信息
     */
    private void setPkColumn(CodeGenTable table) {
        List<CodeGenField> columns = table.getFields();
        boolean exist = false;
        for (CodeGenField column : columns) {
            if (column.getIsPk() == EGeneralYesNo.YES.getCode()) {
                table.setExistId(true);
                table.setIdName(column.getColumnName());
                exist = true;
                break;
            }
        }
        if (!exist) {
            for (CodeGenField column : columns) {
                String columnName = column.getColumnName();
                if (system.getId().equalsIgnoreCase(columnName)) {
                    column.setIsPk(EGeneralYesNo.YES.getCode());
                    table.setExistId(true);
                    table.setIdName(columnName);
                    exist = true;
                    break;
                }
            }
        }
        if (!exist) {
            table.setExistId(false);
        }
    }

    /**
     * 设置编号列信息
     *
     * @param table   业务表信息
     */
    private void setSerialNumberColumn(CodeGenTable table) {
        List<CodeGenField> columns = table.getFields();
        boolean exist = false;
        for (CodeGenField column : columns) {
            String columnName = column.getColumnName();
            if (system.getSerialNumber().equalsIgnoreCase(columnName)) {
                table.setExistSerialNumber(true);
                table.setSerialNumberName(columnName);
                exist = true;
                break;
            }
        }
        if (!exist) {
            table.setExistSerialNumber(false);
        }
    }

    /**
     * 设置删除标志列信息
     *
     * @param table   业务表信息
     */
    private void setDelFlageColumn(CodeGenTable table) {
        List<CodeGenField> columns = table.getFields();
        boolean exist = false;
        for (CodeGenField column : columns) {
            String columnName = column.getColumnName();
            if (system.getDelFlag().equalsIgnoreCase(columnName)) {
                table.setExistDel(true);
                table.setDelName(columnName);
                exist = true;
                break;
            }
        }
        if (!exist) {
            table.setExistDel(false);
        }
    }

    /**
     * 获取数据库类型
     * 
     * @param conn
     * @throws Exception
     */
    private void setDbType(Connection conn) throws Exception {
        setDbType(DbUtil.getDBType(conn));
    }

    /**
     * 获取数据库信息
     * 
     * @param conn
     * @throws Exception
     */
    private void setDatabase(Connection conn) throws Exception {
        // 获取数据库名
        String sql = "SELECT DATABASE();";

        PreparedStatement prepareStatement = null;
        ResultSet executeQuery = null;
        try {
            prepareStatement = conn.prepareStatement(sql);
            executeQuery = prepareStatement.executeQuery();
            if (executeQuery.next()) {
                properties.getDatabase().setDatabaseName(executeQuery.getString(1));
            }
        } finally {
            if (executeQuery != null) {
                executeQuery.close();
            }
            if (prepareStatement != null) {
                prepareStatement.close();
            }
        }
    }

    /**
     * 获取表信息
     * 
     * @param conn
     * @throws Exception
     */
    private void setTable(Connection conn) throws Exception {
        List<CodeGenTable> arrayList = new ArrayList<>();
        // 获取表名
        String sql =
            "SELECT TABLE_NAME,IFNULL(TABLE_COMMENT, '') TABLE_COMMENT,CREATE_TIME,UPDATE_TIME FROM information_schema.TABLES WHERE table_schema='"
                + properties.getDatabase().getDatabaseName() + "' ";
        if (system.getTables().length > 0) {
            StringBuilder tables = new StringBuilder();
            for (String tableName : system.getTables()) {
                tables.append("'" + tableName + "'").append(",");
            }
            tables.replace(tables.lastIndexOf(","), tables.length(), "");
            sql += "AND table_name IN (" + tables.toString() + ")";
        }

        PreparedStatement prepareStatement = null;
        ResultSet executeQuery = null;
        try {
            prepareStatement = conn.prepareStatement(sql);
            executeQuery = prepareStatement.executeQuery();
            while (executeQuery.next()) {
                CodeGenTable table = new CodeGenTable();
                table.setTableName(executeQuery.getString(1));
                table.setTableComment(executeQuery.getString(2));
                arrayList.add(table);
            }
        } finally {
            if (executeQuery != null) {
                executeQuery.close();
            }
            if (prepareStatement != null) {
                prepareStatement.close();
            }
        }
        for (CodeGenTable table : arrayList) {
            try {
                prepareStatement = conn.prepareStatement("SHOW CREATE TABLE " + table.getTableName());
                executeQuery = prepareStatement.executeQuery();
                while (executeQuery.next()) {
                    table.setCreateSql(executeQuery.getString(2));
                }
            } finally {
                if (executeQuery != null) {
                    executeQuery.close();
                }
                if (prepareStatement != null) {
                    prepareStatement.close();
                }
            }
        }
        properties.getDatabase().setTables(arrayList);
    }

    /**
     * 获取字段信息
     * 
     * @param conn
     * @throws Exception
     */
    private void setField(Connection conn) throws Exception {
        String sql =
            "select COLUMN_NAME, COLUMN_COMMENT, COLUMN_TYPE, COLUMN_KEY, case when IS_NULLABLE = 'YES' then '1' else '2' end IS_REQUIRED,"
                + "CASE WHEN EXTRA = 'auto_increment' THEN 1 ELSE 2 END IS_INCREMENT, IF(column_key='PRI',1,2) IS_PK "
                + "from information_schema.columns where table_schema = '" + properties.getDatabase().getDatabaseName()
                + "' and table_name = '%s'";
        for (CodeGenTable table : properties.getDatabase().getTables()) {
            List<CodeGenField> arrayList = new ArrayList<>();
            PreparedStatement prepareStatement = null;
            ResultSet executeQuery = null;
            try {
                prepareStatement = conn.prepareStatement(String.format(sql, table.getTableName()));
                executeQuery = prepareStatement.executeQuery();
                while (executeQuery.next()) {
                    CodeGenField field = new CodeGenField();
                    // ================================================================
                    field.setColumnName(executeQuery.getString(1).toLowerCase());
                    field.setColumnComment(executeQuery.getString(2));
                    String fieldType = executeQuery.getString(3);
                    field.setColumnType(fieldType);
                    if (fieldType.lastIndexOf("(") > 0) {
                        fieldType = fieldType.substring(0, fieldType.lastIndexOf("("));
                    }
                    for (ECodeGenFieldEquals fieldEquals : ECodeGenFieldEquals.values()) {
                        if (fieldType.equalsIgnoreCase(fieldEquals.getDatabase())) {
                            field.setFieldEquals(fieldEquals);
                        }
                    }
                    field.setIsRequired(executeQuery.getInt(5));
                    field.setIsIncrement(executeQuery.getInt(6));
                    field.setIsPk(executeQuery.getInt(7));
                    arrayList.add(field);
                }
                table.setFields(arrayList);

                setPkColumn(table);
                setSerialNumberColumn(table);
                setDelFlageColumn(table);
            } finally {
                if (executeQuery != null) {
                    executeQuery.close();
                }
                if (prepareStatement != null) {
                    prepareStatement.close();
                }
            }
        }
    }

    /**
     * 对字段进行排序
     * 
     * @param arrayList
     * @return
     */
    private void fieldSort() {
        List<CodeGenTable> tables = properties.getDatabase().getTables();
        for (CodeGenTable table : tables) {
            List<CodeGenField> fields = table.getFields();
            List<CodeGenField> arrayList = new ArrayList<>();
            for (CodeGenField field : fields) {
                if (table.getExistId() && table.getIdName().equals(field.getColumnName())) {
                    arrayList.add(field);
                    break;
                }
            }
            for (CodeGenField field : fields) {
                if (table.getExistId() && table.getIdName().equals(field.getColumnName())) {
                    continue;
                }
                arrayList.add(field);
            }
            table.setFields(arrayList);
        }
    }

    /**
     * 初始化表
     */
    private void init() {
        List<CodeGenTable> tables = properties.getDatabase().getTables();
        for (CodeGenTable table : tables) {
            CodeGenUtil.initTable(table);
            List<CodeGenField> fields = table.getFields();
            for (CodeGenField field : fields) {
                CodeGenUtil.initColumnField(field, table);
            }
        }
    }

    /**
     * 获取DbDao
     * 
     * @return
     */
    private static DbDao<?> getDbDao() {
        try {
            DbDao<?> dao = SpringUtil.getBeanByType(DbDao.class);
            if (dao != null) {
                return dao;
            }
            log.info("当前通过在线模式加载代码生成器");
        } catch (Exception e) {
            System.out.println("当前通过离线模式加载代码生成器");
        }
        CONTEXT = new ClassPathXmlApplicationContext("applicationContext.xml");
        return CONTEXT.getBean(DbDao.class);
    }

    /**
     * 获取DataSource
     * 
     * @return
     */
    private static DataSource getDataSource() {
        return CONTEXT.getBean(DataSource.class);
    }

}